# -*- coding:utf-8 -*-
import json
from os import curdir

from pymongo import MongoClient
import pandas as pd
from STool.StockTool import StockTool
from STool.StockTT import *
from SDaily.StockSingle import StockSingle
from STool.StockFileManager import StockFileManager
from stock_py.SysFile import Base_File_Oper
import configparser
class StockSQL:
    
    
    def __init__(self):
        
        # 123.57.153.11:27017  阿里云远端数据库
        # self.client = MongoClient('mongodb://stock_sansha_boll_tushare:hhyy77sg1sldz&@123.57.153.11:27017/tushare')  
        
        self.client = MongoClient('mongodb://127.0.0.1:27017/')       
        self.tushare= self.client.tushare
        self.stock_basic = self.tushare['stock_basic_aa']
        self.stock_account = self.tushare['stock_account']
        self.stock_bankuai_mt = self.tushare['stock_bankuai_mt']
        
        
        self.stock_rps = self.tushare['stock_rps']
        self.stock_all_basic_list=[]
        self.stock_all_bankuai_sw1_stock_count={}
        self.stock_all_bankuai_sw2_stock_count={}
        self.stock_singleObjc = StockSingle()


    
    #获取所有板块信息 ,已申万1级行业做板块跟踪
    def getAllBankuai(self):
        if len(list(self.stock_all_bankuai_sw1_stock_count.keys())) <= 0:
            d1 = {}
            
            for i in self.stock_all_basic_list:                
                if i['hy'] not in d1:
                    d1[i['hy']] = 1
                else:
                    d1[i['hy']] = d1[i['hy']]+1
            self.stock_all_bankuai_sw1_stock_count=d1
            
            d2 = {}
            for i in self.stock_all_basic_list:                
                if i['hy2'] not in d2:
                    d2[i['hy2']] = 1
                else:
                    d2[i['hy2']] = d2[i['hy2']]+1
            self.stock_all_bankuai_sw2_stock_count=d2
            
            
    def searchStockForJijinAndHK(self,index=0):
        #不包含BJ的股票，上市时间超过一年，基金持仓大于3%，或社保持仓大于1%。或北向持股大于10000000万, 市值大于100亿，价格小于150元
        # 获取本地配置，
        config = configparser.ConfigParser()
        config.read(Base_File_Oper.rel_path + 'mainConfig.ini', encoding="utf-8")
        ccc = config.items("mainconfig")  # 获取section的所用配置信息
        print('本地配置：')
        print(ccc)
        get_ST_out = config.getboolean("mainconfig", "剔除ST股票")
        get_BJ_out = config.getboolean("mainconfig", "剔除BJ股票")
        getout_new_time = config.getint("mainconfig", "过滤次新股时间")
        getout_canDeal = config.getint("mainconfig", "基础流通")
        print("RPS SQL 开始查询")
        trade_date_now = self.stock_singleObjc.trade_date_now
        myquery = {

                    "list_date_days":{"$gte": getout_new_time},#上市时间大于1年
                    "total_mv":{"$gte": getout_canDeal},#流通市值大于40亿
                    # "trade_daily.0.trade_date":trade_date_now,  #
                    "list_status":'L' #是否上市状态

                   }
        if get_BJ_out == True:
            myquery["ts_code"] = {"$regex": "(?<!.BJ)$"}  # 不包含BJ
        if get_ST_out == True:
            myquery["name"] = {"$regex": "^((?!ST).)*$"}  # 剔除ST

        rows = self.stock_basic.find(myquery)
        df = pd.DataFrame(list(rows))
        self.stock_all_basic_list=df.to_dict(orient ='records')
        self.getAllBankuai()
 
        df = df.fillna(0)
        df=df.drop(['_id'],axis=1)
        df=df.drop(['trade_weekly_daily'],axis=1)
        trade_daily=df.trade_daily.values
        df['is20Wave']=[item[0]['is20Wave'] for item in trade_daily]
        df['avail_3']=[item[0]['avail_3'] for item in trade_daily]
        df['avail_5']=[item[0]['avail_5'] for item in trade_daily]
        df['avail_20']=[item[0]['avail_20'] for item in trade_daily]
        df['avail_60']=[item[0]['avail_60'] for item in trade_daily]
        df['avail_250']=[item[0]['avail_250'] for item in trade_daily]
        df['isYearHigh']=[item[0]['isYearHigh'] for item in trade_daily]
        df['is20High']=[item[0]['is20High'] for item in trade_daily]
        df['vol']=[item[0]['vol'] for item in trade_daily]#交易量
        df['percent']=[item[0]['percent'] for item in trade_daily]#涨跌幅
        df['open']=[item[0]['open'] for item in trade_daily]
        df['close']=[item[0]['close'] for item in trade_daily]
        df['net_mf_amount']=[item[0]['net_mf_amount'] for item in trade_daily]
        df['buy_elg_amount']=[item[0]['buy_elg_amount'] for item in trade_daily]
        df['buy_elg_percent']=[item[0]['buy_elg_percent'] for item in trade_daily]
        df['huanshou']=[item[0]['huanshou'] for item in trade_daily]

        maxvol_array=[]
        for daily in trade_daily:
            maxvol=0
            for daily_day in daily[1:11]:
                vol = daily_day["vol"]
                if vol>maxvol:
                    maxvol = vol
            maxvol_array.append(maxvol)
        df['maxvol']=maxvol_array #最大成交量
        self.stock_all_basic_list=df.to_dict(orient ='records')
        
        print("sql 数据汇总完成")
        return df
    

    #获取股票信息   
    def getStockInfoWith(self,ts_stock):
        list = [cdict for cdict in self.stock_all_basic_list if cdict["ts_code"] == ts_stock]
        if len(list)>0:
            stock_info = list[0]
            return stock_info            
        return None
    
            

    def getOneStockInfo(self,ts_code):
        myquery = {"ts_code":ts_code}
        stock_local_info = self.stock_basic.find_one(myquery)
        return stock_local_info
            
        
    def sql(self,query):
        response = self.stock_basic.find(query)
        return response
            
        
    def updateBankuaiMt(self,data,date):
        stock_bankuai_local_info = self.stock_bankuai_mt.find_one({ "date": date })
        if stock_bankuai_local_info != None:#本地数据库存在code
            #更新本地 
            newvalues = { "$set": data }    
            self.stock_bankuai_mt.update_one({ "date": date }, newvalues)
        else:
            #本地数据库不存在code
            self.stock_bankuai_mt.insert_one(data)



    def updateStockRPS(self,pd_rps,rps_count=5):
        date=pd_rps['new_date'].values[0]
        columns = pd_rps.columns.tolist()
        if 'hk_ORG' in columns:
            pd_rps=pd_rps.drop(['hk_ORG'],axis=1)
        if 'symbol' in columns:
            pd_rps=pd_rps.drop(['symbol'],axis=1)
        if 'jijin_ORG' in columns:
            pd_rps=pd_rps.drop(['jijin_ORG'],axis=1)
        if 'trade_daily' in columns:
            pd_rps=pd_rps.drop(['trade_daily'],axis=1)
        if 'shebao_ORG' in columns:
            pd_rps=pd_rps.drop(['shebao_ORG'],axis=1)
        
        rps_array = pd_rps.to_dict('records')
        rps_type="rps_"+str(rps_count)
        stock_rps_local_info = self.stock_rps.find_one({ "date": date })

        if stock_rps_local_info != None:#本地数据库存在code
            #更新本地 
            stock_rps_local_info[rps_type]=rps_array
            newvalues = { "$set": stock_rps_local_info }                    
            self.stock_rps.update_one({ "date": date }, newvalues)
        else:
            stock_rps_local_info={"date": date}
            stock_rps_local_info[rps_type]=rps_array
            #本地数据库不存在code
            self.stock_rps.insert_one(stock_rps_local_info)

        
    def getStockFirstInRps(self,rps_count):
        myquery={}
        rows = self.stock_rps.find(myquery,{"date":1,"rps_5":1}).sort("date",-1).limit(2)
        stocks_today=[]
        for item in rows[0]['rps_5']:
            stocks_today.append(item['ts_code'])
           
        stocks_lastday=[]   
        for item in rows[1]['rps_5']:
            stocks_lastday.append(item['ts_code'])
        
        intersection=list(set(stocks_today).intersection(set(stocks_lastday)))
        for i in intersection:
            if i in stocks_today:
                stocks_today.remove(i)

        print("今日新增")
        print(stocks_today)    

        return stocks_today
    
    
    
    
    def searchStockForZhangTing(self):
        #  python 条件
        #  1：前天涨停，或者冲击涨停, 冲击涨停的意思是(最高价==涨停价，收盘价小于最高价, 涨幅超过6%)
        #  2：昨天不涨停
        #  3：今天不涨停
        trade_date_now = self.stock_singleObjc.trade_date_now
        myquery = {
                    "trade_daily.0.limit_status":0,
                    "trade_daily.1.limit_status":0,
                    "trade_daily.2.limit_status":1,
                    "ts_code": {"$regex":"(?<!.BJ)$"},
                    "name":{"$regex":"^((?!ST).)*$"},
                    "trade_daily.0.trade_date":trade_date_now,
                    "list_status":1
                  }
        rows = self.stock_basic.find(myquery)
        df = pd.DataFrame([basic for basic in rows])
        df = df.fillna(0)
        print("板间双日回撤 SQL 查询完成")

        df=df.drop(['_id'],axis=1)
        trade_daily=df.trade_daily.values
        df['is20Wave']=[item[0]['is20Wave'] for item in trade_daily]
        df['avail_3']=[item[0]['avail_3'] for item in trade_daily]
        df['avail_5']=[item[0]['avail_5'] for item in trade_daily]
        df['avail_20']=[item[0]['avail_20'] for item in trade_daily]
        df['avail_60']=[item[0]['avail_60'] for item in trade_daily]
        df['avail_250']=[item[0]['avail_250'] for item in trade_daily]
        df['isYearHigh']=[item[0]['isYearHigh'] for item in trade_daily]
        df['is20High']=[item[0]['is20High'] for item in trade_daily]
        df['vol']=[item[0]['vol'] for item in trade_daily]#交易量
        df['percent']=[item[0]['percent'] for item in trade_daily]#涨跌幅
        df['open']=[item[0]['open'] for item in trade_daily]
        df['close']=[item[0]['close'] for item in trade_daily]
        df['net_mf_amount']=[item[0]['net_mf_amount'] for item in trade_daily]
        df['buy_elg_amount']=[item[0]['buy_elg_amount'] for item in trade_daily]
        df['buy_elg_percent']=[item[0]['buy_elg_percent'] for item in trade_daily]
        df['huanshou']=[item[0]['huanshou'] for item in trade_daily]

        maxvol_array=[]
        for daily in trade_daily:
            maxvol=0
            for daily_day in daily[1:11]:
                vol = daily_day["vol"]
                if vol>maxvol:
                    maxvol = vol
            maxvol_array.append(maxvol)
        df['maxvol']=maxvol_array #最大成交量
        print("sql 数据汇总完成")
        self.stock_all_basic_list=df.to_dict(orient ='records')
        self.getAllBankuai()
        return df
    
    
    
    def searchStockForMA100(self):
        #  python 条件
        #  1：前天涨停，或者冲击涨停, 冲击涨停的意思是(最高价==涨停价，收盘价小于最高价, 涨幅超过6%)
        #  2：昨天不涨停
        #  3：今天不涨停
        trade_date_now = self.stock_singleObjc.trade_date_now
        myquery = {
                    "ts_code": {"$regex":"(?<!.BJ)$"},
                    "name":{"$regex":"^((?!ST).)*$"},
                    "trade_daily.0.trade_date":trade_date_now,
                    "list_date_days":{"$gte":365},#上市时间大于1年
                    "list_status":1,
                    "$and":[{"total_mv":{"$gte":20}},{"total_mv":{"$lte":2000}}],#流通市值大于20亿，小于1000亿
                    "$or":[{"jijin_ORG":{"$gte":0.5}},{"shebao_ORG":{"$gte":0.5}}]
                  }
        rows = self.stock_basic.find(myquery)
        df = pd.DataFrame([basic for basic in rows])
        df = df.fillna(0)
        print('\n')
        print("MA100 SQL 查询完成")

        df=df.drop(['_id'],axis=1)
        trade_daily=df.trade_daily.values
        df['is20Wave']=[item[0]['is20Wave'] for item in trade_daily]
        df['avail_5']=[item[0]['avail_5'] for item in trade_daily]
        df['avail_20']=[item[0]['avail_20'] for item in trade_daily]
        df['avail_60']=[item[0]['avail_60'] for item in trade_daily]
        df['avail_250']=[item[0]['avail_250'] for item in trade_daily]
        df['isYearHigh']=[item[0]['isYearHigh'] for item in trade_daily]
        df['is20High']=[item[0]['is20High'] for item in trade_daily]
        df['vol']=[item[0]['vol'] for item in trade_daily]#交易量
        df['percent']=[item[0]['percent'] for item in trade_daily]#涨跌幅
        df['open']=[item[0]['open'] for item in trade_daily]
        df['close']=[item[0]['close'] for item in trade_daily]
        df['net_mf_amount']=[item[0]['net_mf_amount'] for item in trade_daily]
        df['buy_elg_amount']=[item[0]['buy_elg_amount'] for item in trade_daily]
        df['buy_elg_percent']=[item[0]['buy_elg_percent'] for item in trade_daily]
        df['huanshou']=[item[0]['huanshou'] for item in trade_daily]

        maxvol_array=[]
        for daily in trade_daily:
            maxvol=0
            for daily_day in daily[1:11]:
                vol = daily_day["vol"]
                if vol>maxvol:
                    maxvol = vol
            maxvol_array.append(maxvol)
        df['maxvol']=maxvol_array #最大成交量
        print("sql 数据汇总完成")
        self.stock_all_basic_list=df.to_dict(orient ='records')
        self.getAllBankuai()
        return df
    
    
    def searchAccount(self):
        # myquery = {"$limit": 5}
        rows = self.stock_account.find().limit(20)
        df = pd.DataFrame([basic for basic in rows])
        df=df.drop(['_id'],axis=1)
        return df



    def searchStockForDay7Red(self):
        #  python 条件
        #  1：日线7连阳

        trade_date_now = self.stock_singleObjc.trade_date_now
        myquery = {
                    "ts_code": {"$regex":"(?<!.BJ)$"},
                    "name":{"$regex":"^((?!ST).)*$"},
                    "trade_daily.0.percent":{"$gte":0},
                    "trade_daily.1.percent":{"$gte":0},
                    "trade_daily.2.percent":{"$gte":0},
                    "trade_daily.3.percent":{"$gte":0},
                    "trade_daily.4.percent":{"$gte":0},
                    "trade_daily.5.percent":{"$gte":0},
                    "list_date_days":{"$gte":365},#上市时间大于1年
                    
                  }
        rows = self.stock_basic.find(myquery)
        df = pd.DataFrame([basic for basic in rows])
        df = df.fillna(0)
        print('\n')
        print("7连阳 SQL 查询完成")
        df = df[df['list_date_days']>=365]
        df = df[df['total_mv']>=20]
        
        df=df.drop(['_id'],axis=1)
        trade_daily=df.trade_daily.values
        df['is20Wave']=[item[0]['is20Wave'] for item in trade_daily]
        df['avail_5']=[item[0]['avail_5'] for item in trade_daily]
        df['avail_20']=[item[0]['avail_20'] for item in trade_daily]
        df['avail_60']=[item[0]['avail_60'] for item in trade_daily]
        df['avail_250']=[item[0]['avail_250'] for item in trade_daily]
        df['isYearHigh']=[item[0]['isYearHigh'] for item in trade_daily]
        df['is20High']=[item[0]['is20High'] for item in trade_daily]
        df['vol']=[item[0]['vol'] for item in trade_daily]#交易量
        df['percent']=[item[0]['percent'] for item in trade_daily]#涨跌幅
        df['open']=[item[0]['open'] for item in trade_daily]
        df['close']=[item[0]['close'] for item in trade_daily]
        df['net_mf_amount']=[item[0]['net_mf_amount'] for item in trade_daily]
        df['buy_elg_amount']=[item[0]['buy_elg_amount'] for item in trade_daily]
        df['buy_elg_percent']=[item[0]['buy_elg_percent'] for item in trade_daily]
        df['huanshou']=[item[0]['huanshou'] for item in trade_daily]

        print(len(df))
        print(df['ts_code'])
        return df
    
    
    
    #周线2阳2阴
    def searchZUNI(self):
            #  python 条件
        #  1：日线7连阳

        trade_date_now = self.stock_singleObjc.trade_date_now
        myquery = {
                    "ts_code": {"$regex":"(?<!.BJ)$"}, #剔除bj
                    "name":{"$regex":"^((?!ST).)*$"},  #剔除st
                    "symbol":{"$regex":"^((?!688).)*$"}, #剔除688
                    "trade_weekly_daily.0.percent":{"$lte":0},
                    "trade_weekly_daily.1.percent":{"$gte":0},
                    "trade_weekly_daily.2.percent":{"$lte":0},
                    "trade_weekly_daily.3.percent":{"$gte":0},
                    "list_date_days":{"$gte":365},#上市时间大于1年
                    "ts_code": {"$regex":"(?<!.BJ)$"},
                    "total_mv":{"$lte":1000},#市值小于1000亿
                  }
        rows = self.stock_basic.find(myquery)
        df = pd.DataFrame([basic for basic in rows])
        df = df.fillna(0)
        print('\n')
        print("周线2阳2阴 阻尼 SQL 查询完成")
        if not len(df)>0:
            print("未搜索出阻尼运动股票");
            return
        
        df=df.drop(['_id'],axis=1)

        trade_daily=df.trade_daily.values
        df['is20Wave']=[item[0]['is20Wave'] for item in trade_daily]
        df['avail_5']=[item[0]['avail_5'] for item in trade_daily]
        df['avail_20']=[item[0]['avail_20'] for item in trade_daily]
        df['avail_60']=[item[0]['avail_60'] for item in trade_daily]
        df['avail_250']=[item[0]['avail_250'] for item in trade_daily]
        df['isYearHigh']=[item[0]['isYearHigh'] for item in trade_daily]
        df['is20High']=[item[0]['is20High'] for item in trade_daily]
        df['vol']=[item[0]['vol'] for item in trade_daily]#交易量
        df['percent']=[item[0]['percent'] for item in trade_daily]#涨跌幅
        df['open']=[item[0]['open'] for item in trade_daily]
        df['close']=[item[0]['close'] for item in trade_daily]
        df['net_mf_amount']=[item[0]['net_mf_amount'] for item in trade_daily]
        df['buy_elg_amount']=[item[0]['buy_elg_amount'] for item in trade_daily]
        df['buy_elg_percent']=[item[0]['buy_elg_percent'] for item in trade_daily]
        df['huanshou']=[item[0]['huanshou'] for item in trade_daily]
        df=df.drop(['trade_daily'],axis=1)

        print(len(df))
        # print(df['ts_code'])
        return df
    
    
    
    
    
    
    def searchZhangTing(self):
        trade_date_now = self.stock_singleObjc.trade_date_now
        myquery = {
                    "ts_code": {"$regex":"(?<!.BJ)$"}, #剔除bj
                    "name":{"$regex":"^((?!ST).)*$"},  #剔除st
                    "symbol":{"$regex":"^((?!688).)*$"}, #剔除688
                    "trade_daily.0.limit_status":1,
                    "$and":[{"total_mv":{"$gte":40}},{"total_mv":{"$lte":500}}],
                    "list_date_days":{"$gte":365},#上市时间大于1年
                  }
        rows = self.stock_basic.find(myquery)
        df = pd.DataFrame([basic for basic in rows])
        df = df.fillna(0)
        print('\n')
        print("涨停板 SQL 查询完成")
        if not len(df)>0:
            print("没有涨停板");
            return
        
        df=df.drop(['_id'],axis=1)

        trade_daily=df.trade_daily.values
        df['is20Wave']=[item[0]['is20Wave'] for item in trade_daily]
        df['avail_5']=[item[0]['avail_5'] for item in trade_daily]
        df['avail_20']=[item[0]['avail_20'] for item in trade_daily]
        df['avail_60']=[item[0]['avail_60'] for item in trade_daily]
        df['avail_250']=[item[0]['avail_250'] for item in trade_daily]
        df['isYearHigh']=[item[0]['isYearHigh'] for item in trade_daily]
        df['is20High']=[item[0]['is20High'] for item in trade_daily]
        df['vol']=[item[0]['vol'] for item in trade_daily]#交易量
        df['percent']=[item[0]['percent'] for item in trade_daily]#涨跌幅
        df['open']=[item[0]['open'] for item in trade_daily]
        df['close']=[item[0]['close'] for item in trade_daily]
        df['net_mf_amount']=[item[0]['net_mf_amount'] for item in trade_daily]
        df['buy_elg_amount']=[item[0]['buy_elg_amount'] for item in trade_daily]
        df['buy_elg_percent']=[item[0]['buy_elg_percent'] for item in trade_daily]
        df['huanshou']=[item[0]['huanshou'] for item in trade_daily]
        df=df.drop(['trade_daily'],axis=1)

        print(len(df))
        # print(df['ts_code'])
        return df
    
    
    
#     def choose(arr, item){

#         for(var i = 0; i < arr.length; i++){

#         if(arr[i] == item) return i;

#         }

#         return -1;
# }

    def indexForItem(self,arr,item):
        for index, a in enumerate(arr):
            if a == item:
                return index   
        return -1
        

    def searchBM_MT(self):
        print("11111")
        date_bankuai_rps={}
        rows = self.stock_bankuai_mt.find({"$query": {}, "$orderby": {"$natural": -1}}).limit(10)
        df = pd.DataFrame([basic for basic in rows])
        df = df.fillna(0)
        all_dates = df.trade_date.values
        all_dates = sorted(all_dates)
        bk_datas = df.bk_data.values
        print(df.trade_date.values)

        for index,date in enumerate(all_dates):
            # print(date)
            bk_data = bk_datas[index]
            bk_data = sorted(bk_data,key = lambda e:e.__getitem__('bankuai_mt'), reverse=True)
            # print(bk_data)
            date_bankuai_rps[date]=bk_data[0:20]
            
            if index == len(all_dates)-1:
                cu_bk_data = date_bankuai_rps[all_dates[-1]]
                last_bk_data = date_bankuai_rps[all_dates[-2]]
                last_last_cu_bk_data = date_bankuai_rps[all_dates[-3]]
                
                bk_name = [item['industry']for item in cu_bk_data]
                bk_last_name = [item['industry']for item in last_bk_data]
                bk_last_last_name = [item['industry']for item in last_last_cu_bk_data]
            
                for aaindex,industry in enumerate(bk_name):
                    last_index = self.indexForItem(bk_last_name,industry)
                    last_last_index = self.indexForItem(bk_last_last_name,industry)
                    if aaindex <=last_index and last_index<=last_last_index:
                        color = StockTool.getBrightColor()
                        item = cu_bk_data[aaindex]
                        item['color']=color
                        cu_bk_data[aaindex]=item
                        
                        last_item = last_bk_data[last_index]
                        last_item['color']=color
                        last_bk_data[last_index]=last_item
                        
                        last_last_item = last_last_cu_bk_data[last_last_index]
                        last_last_item['color']=color
                        last_last_cu_bk_data[last_last_index]=last_last_item
        
        # print(date_bankuai_rps)

                        
        all_date_bankuai_rps_pd=pd.DataFrame(date_bankuai_rps)
        bbankuai_data_list = all_date_bankuai_rps_pd.to_dict('records')  # 外层列表，内层是列标题为键值的列表
        
        bbankuai_data_json={}
        bbankuai_data_json['mt']='5'
        bbankuai_data_json['dateList']=list(all_dates)
        bbankuai_data_json['data']=bbankuai_data_list
        

        print(bbankuai_data_json)
        
        StockFileManager.rewriteDataToFile(bbankuai_data_json,"bankuai")